
import define as df
import common as cm
import pandas as pd

def start_record(time, code, history, current, feature):
    sql = '''insert into `record` (`basetime`,`code`,`history`,`current`,`feature`) values 
          ("{time}","{code}","{history}","{current}",{feature})'''.format(time=time,code=code,history=history,current=current,feature=feature)
    try:
        df.conn.execute(sql)
    except Exception, e:
        print('[start_record]{e}'.format(e=e))

def check_record(code, price):
    sql = '''update `record` set `check` = {price}, `checktime`=now() where 
          `code`="{code}" and `basetime`=max(`basetime`)'''.format(code=code, price=price)
    try:
        df.conn.execute(sql)
    except Exception, e:
        print('[check_record]{e}'.format(e=e))

def all_code_in_record():
    sql_code_list = '''select `code` from `record` where `check` is null group by `code`'''
    try:
        q = df.conn.execute(sql_code_list)
        list = q.fetchall()
        result = []
        for item in list:
            result.append(item[0])
        return result
    except Exception, e:
        print('[all_code_in_record]{e}'.format(e=e))
        return None

def check_all_record():
   code_list = all_code_in_record()
   if code_list is None:
       return None

# def buy_record(code, price, number):
#     sql = '''update `record` set `buy` = {price}, `buytime`=now(), `buynumber`={number} where
#           `code`="{code}" and `basetime`=max(`basetime`)'''.format(code=code, price=price, number=number)
#     try:
#         df.conn.execute(sql)
#     except Exception, e:
#         print('[buy_record]{e}'.format(e=e))
#
# def sale_record(code, price, number):
#     sql = '''update `record` set `buy` = {price}, `saletime`=now(), `salenumber`={number} where
#           `code`="{code}" and `basetime`=max(`basetime`)'''.format(code=code, price=price, number=number)
#     try:
#         df.conn.execute(sql)
#     except Exception, e:
#         print('[sale_record]{e}'.format(e=e))
#

def verify_record(code):
    sql = '''select * from `record` where `code`={code} and `check` is not null'''.format(code=code)
    try:
        t = pd.read_sql(sql, con=df.conn)
        t['verify'] = t['check'] - t['feature']
        return t
    except Exception, e:
        print('[verify_record]{e}'.format(e=e))
        return None


def verify_all_record():
    sql = '''select * from `record` where `check` is not null'''
    try:
        t = pd.read_sql(sql, con=df.conn)
        t['verify'] = t['check'] - t['feature']
        return t
    except Exception, e:
        print('[verify_all_record]{e}'.format(e=e))
        return None
